package juhouse.biz.kc.dao;

import hzjbbis.framework.cache.DBUtil;
import hzjbbis.framework.util.dao.JbbisDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import juhouse.biz.kc.form.InvDayBalForm;
import juhouse.framework.tools.DoDate;
import juhouse.framework.tools.Page;

public class InvDayBalDao extends JbbisDao{

	public String[][] getInvDayBalList(int pageno,int rowsize,String areaId)
	{
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		String[][] template = null;
		int i=(pageno-1)*rowsize;
		StringBuffer sqlString = new StringBuffer();
		sqlString.append("select top "+rowsize+" balId,areaId,area,repDate,manageName from invDayBal where available=1 and areaId like ?");
		sqlString.append(" and balId not in (select top "+i+" balId from invDayBal where available=1 and areaId like ? order by repDate desc,areaId) order by repDate desc,areaId");
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString.toString());
			ps.setString(1,areaId+"%");
			ps.setString(2,areaId+"%");
			rs = ps.executeQuery();
			template = DBUtil.getQueryResultStrArray(rs);
		} catch (SQLException sqle) {
			System.out.print(sqle.toString());
		} finally {
			this.close(conn,ps,rs);
		}
		return template;
	}
	public String getInvDayBalListPage(int pageno,int rowsize,int bigpage,String contextPath,String areaId)
	{
		Page page = new Page();
		Connection conn = null; 
		PreparedStatement ps = null;
		ResultSet rs = null;
		String listPage = "",sqlCount="";
		sqlCount="select count(balId) from invDayBal where available=1 and areaId like ?";
		int totalrows = 0;
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlCount.toString());
			ps.setString(1,areaId+"%");
			rs= ps.executeQuery();
			if(rs.next()){
				totalrows = rs.getInt(1);
			}
			if(totalrows>0)
			{
				page.setRowsize(rowsize);
				page.setBigpage(bigpage);
				page.setPageno(pageno);
				page.setTotalrows(totalrows);
				page.excecute(contextPath);
				listPage =  page.toString();
			}
		} catch (Exception sqle) {
			System.out.print(sqle.toString());
		} finally {
			this.close(conn,ps,rs);
		}
		return listPage;
	}
	public String getIsHaveDayBal(String areaId)
	{
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		String flag = "false";
		DoDate myDoDate = new DoDate();
		String thisDate = myDoDate.formatDate(myDoDate.currentlyTime(), "");
		StringBuffer sqlString = new StringBuffer();
		sqlString.append("select balId from invDayBal where available=1 and repDate>='"+thisDate+"' and areaId=?");
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString.toString());
			ps.setString(1,areaId);
			rs = ps.executeQuery();
			if(rs.next())
			{
				flag = "true";
			}
		} catch (SQLException sqle) {
			System.out.print(sqle.toString());
		} finally {
			this.close(conn,ps,rs);
		}
		return flag;
	}
	public int getMaxBalId(String areaId)
	{
		int balId = 0;
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		StringBuffer sqlString = new StringBuffer();
		sqlString.append("select top 1 balId from invDayBal where available=1 and areaId=? order by repDate desc");
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString.toString());
			ps.setString(1,areaId);
			rs = ps.executeQuery();
			if(rs.next())
			{
				balId = rs.getInt(1);
			}
		} catch (SQLException sqle) {
			System.out.print(sqle.toString());
		} finally {
			this.close(conn,ps,rs);
		}
		return balId;
	}
	public String[][] getBalList(int balId)
	{
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		String[][] template = null;
		StringBuffer sqlString = new StringBuffer();
		sqlString.append("select balId,repDate from invDayBal where available=1 and balId=?");
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString.toString());
			ps.setInt(1,balId);
			rs = ps.executeQuery();
			template = DBUtil.getQueryResultStrArray(rs);
		} catch (SQLException sqle) {
			System.out.print(sqle.toString());
		} finally {
			this.close(conn,ps,rs);
		}
		return template;
	}
	public String[][] getListList(int balId)
	{
		PreparedStatement ps = null;
		Connection conn = null;
		ResultSet rs = null;
		String[][] template = null;
		StringBuffer sqlString = new StringBuffer();
		sqlString.append("select listId,buyDate, itemId, itemName, batchNo, inQty, outQty, balQty, brief from invDayBalList where available=1 and balId=?");
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sqlString.toString());
			ps.setInt(1,balId);
			rs = ps.executeQuery();
			template = DBUtil.getQueryResultStrArray(rs);
		} catch (SQLException sqle) {
			System.out.print(sqle.toString());
		} finally {
			this.close(conn,ps,rs);
		}
		return template;
	}
	public void newSaveInvDayBal(InvDayBalForm invDayBalForm,String areaId,String area,String manageName)
	{
		PreparedStatement ps = null;
		Connection conn = null;
		StringBuffer sqlString = new StringBuffer();
		sqlString.append("insert into invDayBal (balId,areaId,area,manageName,repDate,AVAILABLE)values(?,?,?,?,?,1)");
		int j =1;
		int balId = invDayBalForm.getBalId();
		try{
			conn = this.getConnection();
			if(balId>0)
			{
				this.delInvDayBal(conn, balId);
			}else{
				balId = this.recordKey(conn);
			}
			
			ps = conn.prepareStatement(sqlString.toString());
			ps.setInt(j++,balId);
			ps.setString(j++,areaId);
			ps.setString(j++,area);
			ps.setString(j++,manageName);
			ps.setString(j++,invDayBalForm.getRepDate());
			ps.executeUpdate();
			this.newSaveInvDayBalList(conn,balId, invDayBalForm);
			conn.commit();
		}catch (SQLException e)
		{
			System.out.print("\nnewSaveSchool:"+e.toString());
		}finally{
			this.close(conn,ps);
		}
	}
	private void newSaveInvDayBalList(Connection conn0,int balId,InvDayBalForm invDayBalForm)
	{
		int[] listId      = invDayBalForm.getListId();
		String[] buyDate  = invDayBalForm.getBuyDate();
		String[] itemId      = invDayBalForm.getItemId();
		String[] itemName = invDayBalForm.getItemName();
		String[] batchNo  = invDayBalForm.getBatchNo();
		float[] inQty    = invDayBalForm.getInQty();
		float[] outQty   = invDayBalForm.getOutQty();
		float[] balQty   = invDayBalForm.getBalQty();
		String[] brief     = invDayBalForm.getBrief();
		for(int i=0;i<listId.length;i++)
		{
			if(!itemName[i].equals(""))
			{
				PreparedStatement ps = null;
				StringBuffer sqlString = new StringBuffer();
				sqlString.append("INSERT INTO invDayBalList(balId, buyDate, itemId, itemName, batchNo, inQty, outQty, balQty, brief, AVAILABLE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 1)");
				int j =1;
				try{
					ps = conn0.prepareStatement(sqlString.toString());
					ps.setInt(j++,balId);
					String buyDate1 = buyDate[i]; if(buyDate1.equals("")) buyDate1 = null;
					ps.setString(j++,buyDate1);
					ps.setString(j++,itemId[i]);
					ps.setString(j++,itemName[i]);
					ps.setString(j++,batchNo[i]);
					ps.setFloat(j++,inQty[i]);
					ps.setFloat(j++,outQty[i]);
					ps.setFloat(j++,balQty[i]);
					ps.setString(j++,brief[i]);
					ps.executeUpdate();
				}catch (SQLException e)
				{
					System.out.print("\nnewSaveInvDayBalList:"+e.toString());
				}finally{
					this.close(ps);
				}
			}
		}
		
	}
	private void delInvDayBal(Connection conn0,int balId)
	{
		PreparedStatement ps = null;
		
		StringBuffer sqlString = new StringBuffer();
		sqlString.append(" delete from invDayBalList where balId= "+balId );
		sqlString.append(" delete from invDayBal where balId= "+balId);
		try{
			ps = conn0.prepareStatement(sqlString.toString());
			ps.executeUpdate();
		}catch(SQLException e){
			System.out.print("\ndelInvDayBal:"+e.toString());
		}finally{
			this.close(ps);
		}
	}
	private int recordKey(Connection con){
		int key=0;
		StringBuffer str = new StringBuffer();
		Statement st = null;
		ResultSet rs = null;
		str.append("select MAX(balId) from invDayBal");
		try {
			st = con.createStatement();
			rs = st.executeQuery(str.toString());
			if(rs.next()){
				key = rs.getInt(1)+1;
			}
			else {
				key = 200000001;
			}
		} catch (SQLException sqle) {
			System.out.print(sqle.toString());
		}
		finally{
			close(st,rs);
		}
		return key;
	}
	public void deleteInvDayBal(int balId)
	{
		Connection conn = null;
		try{
			conn = this.getConnection();
			this.delInvDayBal(conn, balId);
			conn.commit();
		}catch (SQLException e)
		{
			System.out.print("\ndeleteInvDayBal:"+e.toString());
		}finally{
			this.close(conn);
		}
	}
}
